﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DONGACS.Common;
using System.Data;

namespace DONGACS.DAO
{
    public class DoanRaDao : AccessData
    {
        public DataTable GetDoanRaByIdReport(string id)
        {
            CommandText = "Select * from dr_DoanRa dr left join  dr_DanhMucKinhPhi kp on  dr.IdKinhPhi = kp.Id left join dr_DanhMucMucDich md  on dr.IdMucDich = md.Id left join dm_Nuoc n on dr.IdNuocDen = n.Id  Where dr.Id= " + id;
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }
          public DataTable GetHoSoDinhKemByIdReport(string id)
        {
            CommandText = "Select * from dr_DanhMucLoaiHoSoDinhKem Dm_LDK, dr_CauHinhHoSoDinhKem CH   Where Dm_LDK.Id = CH.IdLoaiHSDK  and CH.IdLoaiDR = " + id;
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }
        public DataTable GetDoanRaByIdBienNhan(string id)
        {
            CommandText = "Select * from dr_DoanRa dr  Where  dr.Id = " + id;
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }
        public DataTable GetThanhPhanDoanByIdDoanRaReport(string id)
        {
            CommandText = "Select tpd.Id,tpd.SoHoChieu,tpd.IdDoanRa,tpd.HoTen,tpd.ChucVu,tpd.DonViCongTac,tpd.DangVien,tpd.HoTen, lcc.ten as Loai ,ncc.ten as Ngach ,b.ten as MaNgach,tpd.BacLuong "+
                "from dr_ThanhPhanDoan tpd left join dm_LoaiCongChuc lcc on tpd.LoaiCongChuc=lcc.Id left join dm_NgachCongChuc ncc on tpd.NgachCongChuc=ncc.Id left join dm_Bac b on tpd.IdBac=b.Id "
            + "Where tpd.IdDoanRa =" + id;
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }

        public DataTable GetThongTinHoChieuBySoHoChieu(string SoHoChieu)
        {
            CommandText = "Select hc.SHC,dtql.HoTen, dtql.NgaySinh,dtql.GioiTinh,hc.NgayCap,hc.NoiCap,dtql.DangVien,dv.TenDonVi from HoChieu hc,DoiTuongQuanLy dtql, DonVi dv Where SHC = " + SoHoChieu
                + " and hc.IdDoiTuongQuanLy =dtql.Id and dtql.IdDonVi=dv.Id";
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }

        public DataTable GetNoiDungBaoCaoByIdDoanRaReport(string id)
        {
            CommandText = "Select * from dr_BaoCaoNhacNho Where IdDoanRa = " + id;
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
                return table;
        }

        public  DataTable GetAllDanhSachDoanRaTheoNuoc(DateTime TuNgay, DateTime DenNgay)
        {
            CommandText = "select *,e.Ten as TenKinhPhi from dr_DoanRa a INNER JOIN dr_ThanhPhanDoan b " +
                          "on a.Id = b.IdDoanRa inner join dr_DanhMucMucDich c on a.IdMucDich = c.Id" +
                          " inner join LoaiHoChieu d on d.Id = b.IdLoaiHochieu inner join dr_DanhMucKinhPhi e on e.Id = a.IdKinhPhi" +
                          " where a.NgayDi >='" + TuNgay.ToString("yyyy/MM/dd") + "' and a.NgayVe <= '" + DenNgay.ToString("yyyy/MM/dd") + "'";
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }

        public DataTable BaoCaoTongHopDoanRa(DateTime TuNgay, DateTime DenNgay)
        {
            CommandText = "select COUNT(a.id)  as TongSoDoanOfMucDich,(select count(id) from dv_HoSo " +
            "where NgayDi >='" + TuNgay.ToString("yyyy/MM/dd") + "' and NgayVe <= '" + DenNgay.ToString("yyyy/MM/dd") + "') as SoDoanVao," +
            "b.Ten from dv_HoSo a inner join dv_DanhMucMucDich b on a.IdMucDich = b.Id" +
            " where a.NgayDi >='" + TuNgay.ToString("yyyy/MM/dd") + "' and a.NgayVe <= '" + DenNgay.ToString("yyyy/MM/dd") + "' group by IdMucDich,b.Ten";
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }

        public DataTable BaoCaoTongHopDoanRao(DateTime TuNgay, DateTime DenNgay)
        {
            CommandText = "select COUNT(a.id)  as TongSoDoanOfMucDich,a.IdMucDich,a.IdKinhPhi,(select count(id) from dr_DoanRa where NgayDi >='" 
                + TuNgay.ToString("yyyy/MM/dd") + "' and NgayDi <= '" + DenNgay.ToString("yyyy/MM/dd") + "') as SoDoanRa," +
            "b.Ten as TenMucDich,c.Ten as TenKinhPhi from dr_DoanRa a inner join dr_DanhMucMucDich b on a.IdMucDich = b.Id"+
            " join dr_DanhMucKinhPhi c on a.IdKinhPhi = c.Id  where a.NgayDi >='" + TuNgay.ToString("yyyy/MM/dd") + "' and a.NgayDi <= '" 
            + DenNgay.ToString("yyyy/MM/dd") + "' group by IdMucDich,b.Ten,c.Ten,a.IdKinhPhi";
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }
        public DataTable BaoCaoTongHopDoanRaoKinhPhi(DateTime TuNgay, DateTime DenNgay)
        {
            CommandText = "select COUNT(a.id)  as TongSoDoanOfKinhPhi,a.IdKinhPhi,"+"(select count(id) from dr_DoanRa where NgayDi >='"
                + TuNgay.ToString("yyyy/MM/dd") + "' and NgayDi <= '" + DenNgay.ToString("yyyy/MM/dd") + "' and TrangThaiBaoCao = 1) as DaBaoCao,"
                + "(select count(id) from dr_DoanRa where NgayDi >='"+ TuNgay.ToString("yyyy/MM/dd") + "' and NgayDi <= '" + DenNgay.ToString("yyyy/MM/dd") + "') as SoDoanRa,"
                +"c.Ten as TenKinhPhi from dr_DoanRa a inner join dr_DanhMucKinhPhi c on a.IdKinhPhi = c.Id "
                + "where a.NgayDi >='" + TuNgay.ToString("yyyy/MM/dd") + "' " + "and a.NgayDi <= '" + DenNgay.ToString("yyyy/MM/dd") + "' group by a.IdKinhPhi,c.Ten";
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }
    }
}